﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data; 
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data.Common;
using System.Xml;
using System.Configuration;
namespace DAO
{
   public static class DaTaProvider
    {
       // private static string _connectionstring = DaTaProvider.ReadConnectionString("ConnectionString.xml");
       //private static string _connectionstring = System.Configuration.ConfigurationManager.AppSettings["connection1"];
       private static string _connectionstring = ConfigurationManager.AppSettings["connection"];
        public static string ConnectionString
        {
            //get { return DaTaProvider.ReadConnectionString("ConnectionString.xml"); }
            get {
                return System.Configuration.ConfigurationManager.AppSettings["connection"]; 
               //return ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
            }
            set { DaTaProvider._connectionstring = value; }
        }

        static SqlConnection connection;
        static SqlCommand command;
       static SqlDataAdapter adapter;

   // #endregion Convert DaTaReader To DaTaTable
       public static DataTable ConvertDataReaderToDataTable(IDataReader reader, bool bClose)
       {
           if (reader == null)
               return null;
           DataTable objDataTable = new DataTable();
           int intFieldCount = reader.FieldCount;
           int intCounter;
           for (intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
           {
               objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
           }
           objDataTable.BeginLoadData();
           object[] objValues = new object[intFieldCount];
           while (reader.Read())
           {
               reader.GetValues(objValues);
               objDataTable.LoadDataRow(objValues, true);
           }
           if (bClose)
           {
               reader.Close();
           }
           objDataTable.EndLoadData();
           return objDataTable;
       }
//#endregion
        #region connect to data
        public static bool Checkconnect()
        {
            try
            {
                connection = new SqlConnection( _connectionstring );
                connection.Open();
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    return true;
                }
            }
            catch ( SqlException )
            {
                connection.Close();
                return false;
            }
            return false;
        }

        public static bool Checkconnect(string sqlConnectionString)
        {
            if (String.IsNullOrEmpty(sqlConnectionString))
                return false;
            try
            {
                connection = new SqlConnection( sqlConnectionString );
                connection.Open();
                    if( ConnectionState.Open == connection.State )
                        return true;
            }
            catch( SqlException )
            {
                connection.Close();
                return false;
            }
            return false;

        }
        #endregion

        #region disconnect to data
        public static void disconnect()
        {
            if (connection != null)
                connection.Close();
        }
        #endregion 

        #region thuc hien truy van 
        public static  DataTable  excecuteQuery(string sqlstring)
        {
            
            connection = new SqlConnection( ConnectionString );

            DataTable table = new DataTable();
            connection.Open();
            if (connection.State == ConnectionState.Open)
            {
                try
                {

                    command = connection.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = sqlstring;

                    
                    SqlDataAdapter adapter = new SqlDataAdapter(command.CommandText, connection);

                    SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
                   
                    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                    adapter.Fill(table);
                   // string test = table.Rows[0]["maHocSinh"].ToString();
                  /*  SqlDataReader result = command.ExecuteReader();
                    //tb = ConvertDataReaderToDataTable( result, false );

                    DataTable dtble = result.GetSchemaTable();
                    int itemp = result.FieldCount;
                    
                    for (int i = 0; i < itemp; i++)
                    {

                        DataRow dataRow = dtble.Rows[i];
                        string columnName = dataRow["ColumnName"].ToString();
                        Type test =Type.GetType(       dataRow["Datatype"].ToString()        );
                        DataColumn column = new DataColumn(columnName, test );
                        DataTable ete = dataRow.Table;
                        tb.Columns.Add(column);
                    }
                        //  SqlDataAdapter adt = new SqlDataAdapter( sqlstring, connection );
                         // adt.Fill(tb);
                          //DataGridView t = new DataGridView();
                         // t.DataSource = tb;
                        // tb.Rows.Add( dtr );
                  //  tb.Load( result );
                    while (result.Read())
                    {
                        DataRow dataRow = tb.NewRow();
                        for (int i = 0; i < itemp; i++)
                        {
                            dataRow[i] = result.GetValue( i );
                        }
                        tb.Rows.Add(dataRow);
                    }
                    */
                        connection.Close();
                    return table;

                }
                catch (SqlException ex)
                {
                    connection.Close();
                    throw ex;
                }
            }
            connection.Close();
            return table;
           
        }
        #endregion

        public static int ExcecuteNonQuery(string sqlstring)
        {
            try
            {
                connection = new SqlConnection( _connectionstring );
                connection.Open();
                
            }
            catch (SqlException ex)
            {
                connection.Close();
                return 0;
            }
            int n = 0;
            if (connection.State == ConnectionState.Open)
            {
                try
                {
                    command = connection.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = sqlstring;
                    
                    n = command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    connection.Close();
                    return 0;
                }
                
            }
            if( ConnectionState.Open == connection.State ) 
                connection.Close();
            return n;

            

        }


        public  static string ReadConnectionString( string FileName )
        {
            //try
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(FileName);
                XmlElement root = doc.DocumentElement;
                _connectionstring = root.InnerText;
                return _connectionstring;
            }
            //catch (Exception ex)
            {
                //return null;
              //  throw ex;
            }
        }

        public static string ExecuteScalar( string sqlString )
        {
            string kq ;
            try
            {
                connection = new SqlConnection(ConnectionString);
                connection.Open();
            }
            catch (SqlException ex)
            {
                connection.Close();
                throw ex;
            }

            try
            {
                command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = sqlString;

                kq = command.ExecuteScalar().ToString();

            }
            catch (SqlException ex)
            {
                connection.Close();
                throw ex;
            }

            connection.Close();
            return kq;

        }


       



    }
}
